using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using System.Xml;

using Antix;

namespace Antix.Data.Catalogue.Objects
{
    /// <summary>
    /// Database Table
    /// </summary>
    public class DatabaseTable : DatabaseObject
    {
        #region Constructor/Defaults

        /// <summary>
        /// Create a new table
        /// </summary>
        /// <param name="server"></param>
        internal DatabaseTable(Database database)
            : this("dbo", "(new table)", database) { base.IsNew = true; }

        /// <summary>
        /// Create an existing table object
        /// </summary>
        internal DatabaseTable(string schemaName, string name, Database database)
            : base(name)
        {
            this.Database = database;
            _Schema = database.Schemas[schemaName, true];
            _SchemaOriginal = _Schema;

            _Columns = null;
            _Indexes = null;
        }

        #endregion

        #region Properties

        #region Key

        /// <summary>
        /// Key for this object
        /// </summary>
        public override string Key
        {
            get { return GetKey(this.Schema.Name, this.Name); }
        }

        #endregion

        #region Database

        internal readonly Database Database;

        #endregion

        #region Schema

        private DatabaseSchema _Schema;

        /// <summary>
        /// Schema for this table
        /// </summary>
        public DatabaseSchema Schema
        {
            get { return _Schema; }
            set
            {
                if (_Schema != value)
                {
                    _Schema = value;
                    base.IsEdited = true;
                }
            }
        }

        private DatabaseSchema _SchemaOriginal;

        /// <summary>
        /// Original Schema for this table
        /// </summary>
        public DatabaseSchema SchemaOriginal
        {
            get { return _SchemaOriginal; }
            internal set
            {
                if (_SchemaOriginal != value)
                {
                    _SchemaOriginal = value;
                    base.IsEdited = true;
                }
            }
        }

        #endregion

        #region Columns

        private DatabaseColumnCollection _Columns;

        /// <summary>
        /// Columns
        /// </summary>
        public DatabaseColumnCollection Columns
        {
            get
            {
                if (this._Columns == null)
                {
                    // load on demand
                    this._Columns = new DatabaseColumnCollection(this);
                    this.Database.LoadColumns(this);
                }
                return this._Columns;
            }
        }

        #endregion

        #region Indexes

        private DatabaseIndexCollection _Indexes;

        /// <summary>
        /// Indexes
        /// </summary>
        public DatabaseIndexCollection Indexes
        {
            get
            {
                if (this._Indexes == null)
                {
                    // load on demand
                    this._Indexes = new DatabaseIndexCollection(this);
                    this.Database.LoadIndexes(this);
                }
                return this._Indexes;
            }
        }

        #endregion

        #endregion

        #region Methods

        #region Script the table

        /// <summary>
        /// Generate script for this table
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(StringBuilder sql, bool subObjects)
        {
            return this.Database.Script(this, sql, subObjects);
        }

        #endregion

        #region GetKey

        /// <summary>
        /// Get the key for the database table
        /// </summary>
        /// <param name="schema">Schema</param>
        /// <param name="name">Name</param>
        /// <returns>Key</returns>
        public static string GetKey(string schemaName, string name)
        {
            return schemaName + "." + name;
        }

        #endregion

        #region Import

        /// <summary>
        /// Import an xml file into this table
        /// </summary>
        public void ImportXml(string file, SqlConnection conn)
        {
            XmlReaderSettings readerSettings = new XmlReaderSettings();
            readerSettings.IgnoreWhitespace = true;
            readerSettings.IgnoreComments = true;

            XmlReader reader = XmlReader.Create(file, readerSettings);

            while (reader.Read())
            {
                // goto first element
                if (reader.Depth == 1)
                {
                    // read sub elements
                    reader.Read();

                    // create the command for the update
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;

                    string columns = "";
                    string values = "";
                    while (reader.Depth > 1)
                    {
                        // Column name
                        DatabaseColumn column = this.Columns[reader.Name];
                        columns += "[" + column.Name + "],";
                        values += "@" + column.Name + ",";
                        reader.Read();

                        // column value
                        cmd.Parameters.Add("@" + column.Name, column.SqlDbType).Value = reader.Value;

                        reader.Read();
                        reader.Read();
                    }

                    cmd.CommandText
                        = "INSERT INTO [" + this.Schema.Name + "].[" + this.Name + "]"
                            + "(" + columns.TrimEnd(',') + ")"
                            + "VALUES(" + values.TrimEnd(',') + ")";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #endregion
    }
}